package com.servlet.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.servlet.dao.UserDao;
import com.servlet.entity.User;
import com.servlet.util.DBUtil;


public class UserDaoImpl implements UserDao {
	
	List<User> list = new ArrayList<>();
	Connection conn = DBUtil.getConnection();
	PreparedStatement ps = null;
	ResultSet rs = null;
	/**
	 * 查询用户
	 */
	@Override
	public List<User> getListAll() {		
		try {
			String sql = "select * from user";
			  ps = conn.prepareStatement(sql);
			  rs = ps.executeQuery();
			 while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setAge(rs.getString(3));
				user.setAddrs(rs.getString(4));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 添加用户
	 */
	@Override
	public int saveUser(User user) {
		int count = 0;//返回成功添加的数量
		String sql = "insert into user(name,age,addrs) values(?,?,?)";
		try {
			 ps = conn.prepareStatement(sql);
			 ps.setString(1, user.getName());
			 ps.setString(2, user.getAge());
			 ps.setString(3, user.getAddrs());
			 count = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return count;
	}
	
	/**
	 * 根据id删除用户
	 * @return 
	 */
	@Override
	public int deleteUser(Integer id) {
		int count = 0;
		try {
			String sql = "delete from user where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			count = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return count;
	}
	
	/**
	 * 更新用户
	 * @return 
	 */
	@Override
	public int updateUser(User user) {
		int count = 0;
		String sql = "update user set name=?,age=?,addrs=? where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getName());
			ps.setString(2, user.getAge());
			ps.setString(3, user.getAddrs());
			ps.setInt(4, user.getId());
			count = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * 根据id查询用户
	 */
	@Override
	public User getById(Integer id) {
		User user = null;
		String sql = "select * from user where id = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getString("age"));
				user.setAddrs(rs.getString("addrs"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return user;
	}

}
